rm(list=ls())
gc()
path = "/Users/an588/" # Laptop
# path = "D:/" # Desktop
library(haven) # read in .dta
library(lfe)
library(dplyr)
library(stargazer)
library(tidyr)
library(stringr)
library(readxl)
library(data.table)
library(xtable)
library(fixest)
library(DescTools)
library("writexl")
library(dplyr)
library(janitor)
library(arrow)
#----------------------------------------------------------------------------------
# Define functions
#----------------------------------------------------------------------------------
'%!in%' <- function(x,y)!('%in%'(x,y))
#----------------------------------------------------------------------------------
# Load the data
#----------------------------------------------------------------------------------
crosswalk = read_excel( paste0( path, "Dropbox/January_2022/2. Data/Cleaned crosswalk data/crosswalk_atlas_cusips.xlsx" ) )
crosswalk$state_fips = as.numeric(crosswalk$state_fips)
crosswalk$entity_code = as.numeric(crosswalk$entity_code)
crosswalk$identif = paste0( crosswalk$state_fips, "_", crosswalk$entity_code )
crosswalk = unique(crosswalk)
party_history = read_dta(paste0( path, "test Dropbox/Anya Nakhmurina/January_2022/2. Data/Cleaned election data/Party_History_DN_LN_cleaned_w_switch.dta"))
party_history$state_fips = as.numeric(party_history$state_fips)
party_history$entity_code = as.numeric(party_history$entity_code)
party_history$identif = paste0( party_history$state_fips, "_", party_history$entity_code )
data = party_history %>% dplyr::select( "state_fips" , "entity_code" , "identif" , "party_code" ,"switch_2019",
"beginyr", "endyr"  )
# Minor edit for NC 37_31400 High Point city
data$beginyr = ifelse( data$beginyr > data$endyr, data$endyr, data$beginyr )
data = left_join(data, crosswalk )
city_year_party_panel = setDT(data)[ , list( state_fips = state_fips, entity_code = entity_code,
identif = identif,
stateabbv = stateabbv,
cusip6 = cusip6,  obligorname = obligorname,
party_code = party_code, switch_2019 = switch_2019,
year = seq(beginyr, endyr, by = 1)
), by = 1:nrow(data) ]
city_year_party_panel$nrow = NULL
city_year_party_panel = unique( city_year_party_panel )
city_year_party_panel$entity_code = as.character( city_year_party_panel$entity_code )
crosswalk$entity_code = as.character( crosswalk$entity_code )
# city_year_party_panel$state_fips = as.character( city_year_party_panel$state_fips )
city_year_party_panel = left_join(city_year_party_panel, crosswalk )
city_year_party_panel$state_fips = as.numeric(city_year_party_panel$state_fips)
# remove observations post 2019 (mechanically collected for some cities)
city_year_party_panel = city_year_party_panel %>% filter( year >= 2005 & year <= 2019 )
# Add gubernatorial election variable:
governors = read_excel( paste0(  path, "test Dropbox/Anya Nakhmurina/January_2022/2. Data/Governor/governor_panel_with_names.xlsx" ) )
governors = governors %>% dplyr::select(stateabbv, state_fips,  year, GovParty ) %>% unique()
governors$gov_party_code = ifelse( governors$GovParty == "DEMOCRAT", 1, 2 )
governors$gov_party_code = ifelse( governors$GovParty == "OTHER", 3, governors$gov_party_code )
city_year_party_panel = left_join(city_year_party_panel, governors)
city_year_party_panel$alignGovernor = ifelse( city_year_party_panel$party_code == city_year_party_panel$gov_party_code, 1, 0 )
#-----------------------------------------------------------------
# Load our secondary trading data
#----------------------------------------------------------------
trading_data = read_feather("/Users/an588/test Dropbox/Anya Nakhmurina/January_2022/2.1 Cleaned data/secondary_data/monthly_mma_2005_2019.feather") # 18162029 obs
# restrict to non-taxable bonds
trading_data = trading_data %>% filter( tax_exempt == 1 ) # 17017012
#restrict to non-callable bonds
trading_data = trading_data %>% filter( callable == 0 ) #6715460 !!! 60% drop
state_tax = read_dta("/Users/an588/test Dropbox/Anya Nakhmurina/January_2022/2.2 Dataset for Analysis/state_tax.dta")
state_crosswalk = read_excel("/Users/an588/test Dropbox/Anya Nakhmurina/January_2022/2. Data/Cleaned crosswalk data/state_level_crosswalk.xlsx")
state_tax = left_join(state_tax, state_crosswalk )
state_tax$state_c = state_tax$stateabbv
trading_data = left_join(trading_data, state_tax, by = c("state_c", "year") )
# Create spread variables (double checked that these variables are correct -- identical to the ones created by Ramona):
trading_data$SPREAD_MMAinterp = trading_data$yield - trading_data$mma_linear_interp
trading_data$taxSPREAD_MMAinterp = trading_data$yield/0.65 - trading_data$mma_linear_interp
trading_data$statetaxSPREAD_MMAinterp = (trading_data$yield )/(1-trading_data$state_income_tax_max/100)- trading_data$mma_linear_interp
trading_data$lnmat = log( trading_data$time_to_maturity )
trading_data$matround = trading_data$yrstomat
########### Add ratings data ####################
ratings = read_dta("/Users/an588/test Dropbox/Anya Nakhmurina/January_2022/2. Data/Ratings aggregated/ratings_all_2023.dta")
ratings <- ratings %>% rename(cusip = cusip_c)
trading_data = left_join(trading_data, ratings )
trading_data$rating_average_under = ifelse( is.na(trading_data$rating_average_under), 0, trading_data$rating_average_under )
########### Add cities-level sociodemographic info ####################
trading_data = trading_data %>% dplyr::select( -c(  state_fips, stateabbv ) )
trading_data = left_join(trading_data, crosswalk)
municipalities_census = read_dta("/Users/an588/test Dropbox/Anya Nakhmurina/January_2022/3. Code/municipalities_census_short.dta")
municipalities_census = municipalities_census %>% filter( year >= 2004 & year <= 2019 )
municipalities_census$identif = paste0( municipalities_census$state_fips, "_", municipalities_census$entity_code )
panel <- expand.grid(year = 2004:2019,
identif = unique(municipalities_census$identif),
stringsAsFactors = FALSE)
municipalities_census = left_join(panel, municipalities_census )
# Create variables that we use in regressions:
# Natural Log of population
municipalities_census$lnpop = log( municipalities_census$population )
# Create lagged revenue and lagged debt:
municipalities_census <- municipalities_census %>% arrange(identif, year)
# Create lagged revenue
municipalities_census <- municipalities_census %>%
group_by(identif) %>%
mutate(lagged_total_revenue = lag(total_revenue, n = 1),
lagged_total_debt_outstanding = lag(total_debt_outstanding, n = 1),
lagged_population = lag(population, n = 1),
) %>%
ungroup()
municipalities_census$scaled_lag_total_revenue = municipalities_census$lagged_total_revenue/municipalities_census$lagged_population
municipalities_census$scaled_lag_total_debt_out = municipalities_census$lagged_total_debt_outstanding/municipalities_census$lagged_population
# # drop varibles that we don't need
# municipalities_census = municipalities_census %>%
#   dplyr::select( -c( name, state_fips, fips_county, entity_code, yearpop ) )
trading_data$entity_code = as.numeric(trading_data$entity_code)
trading_data = left_join(trading_data, municipalities_census )
########### Checking HERE ####################
trading_data = trading_data %>% dplyr::select(-c(stateabbv, state_fips, entity_code, obligorname, identif))
# Create non-swtichers with only two parties data
non_switchers = city_year_party_panel %>% filter( switch_2019 == 0 & party_code <= 2)
non_switchers$entity_code = as.numeric( non_switchers$entity_code )
non_switchers = left_join( trading_data , non_switchers )
non_switchers = non_switchers %>% filter( !is.na( party_code ) ) #1457064
# Create non-switcher with all party data
non_switchers_all = city_year_party_panel %>% filter( switch_2019 == 0 )
non_switchers_all$entity_code = as.numeric( non_switchers_all$entity_code )
non_switchers_all = left_join( trading_data , non_switchers_all )
non_switchers_all = non_switchers_all %>% filter( !is.na( party_code ) ) #1744271
# Create data for both switchers and non-switchers:
city_year_party_panel$entity_code = as.numeric( city_year_party_panel$entity_code )
all = left_join( trading_data , city_year_party_panel )
all = all %>% filter( !is.na( party_code ) ) #2845092
# Select variables that we'll need for regressions and order them:
all = all %>% dplyr::select( state, stateabbv,state_fips, entity_code, identif, obligorname, cusip6,  cusip,  # IDs
year,
party_code, GovParty,  gov_party_code, alignGovernor, switch_2019, # Political variables
statetaxSPREAD_MMAinterp, statetaxSPREAD_TRSYinterp,
taxSPREAD_MMAinterp, taxSPREAD_TRSYinterp, SPREAD_TRSYinterp, SPREAD_MMAinterp, statefederalSPREAD_TRSYinterp,
yield, ret, absret, ret_mkt, effective_spread, effective_spread_retail, effective_spread_institutional,
rating_average_under, lnsize,
lnmat_at_issuance, time_to_maturity, matround, lnmat, BQ, GO, insured_mergent,
issue_size,
callable, federal_tax_exempt, state_tax_exempt, n_cusips_in_issue, reoffered_i,
total_maturity_offering_amt_f,total_offering_amount_f,
lnpop,  population, lagged_population,
scaled_lag_total_revenue, total_revenue, lagged_total_revenue,
scaled_lag_total_debt_out,total_debt_outstanding,  lagged_total_debt_outstanding,
total_ig_revenue, total_rev_own_sources, total_expenditure,
use_of_proceeds_c, default_flag_i, material_event_flag_i
)
# Select variables that we'll need for regressions and order them:
all = all %>% dplyr::select( state, stateabbv,state_fips, entity_code, identif, obligorname, cusip6,  cusip,  # IDs
year,
party_code, GovParty,  gov_party_code, alignGovernor, switch_2019, # Political variables
statetaxSPREAD_MMAinterp,
taxSPREAD_MMAinterp, SPREAD_MMAinterp,
yield, ret, absret, ret_mkt, effective_spread, effective_spread_retail, effective_spread_institutional,
rating_average_under, lnsize,
lnmat_at_issuance, time_to_maturity, matround, lnmat, BQ, GO, insured_mergent,
issue_size,
callable, federal_tax_exempt, state_tax_exempt, n_cusips_in_issue, reoffered_i,
total_maturity_offering_amt_f,total_offering_amount_f,
lnpop,  population, lagged_population,
scaled_lag_total_revenue, total_revenue, lagged_total_revenue,
scaled_lag_total_debt_out,total_debt_outstanding,  lagged_total_debt_outstanding,
total_ig_revenue, total_rev_own_sources, total_expenditure,
use_of_proceeds_c, default_flag_i, material_event_flag_i
)
# write_dta(all, paste0( path, "test Dropbox/Anya Nakhmurina/January_2022/2.2 Dataset for Analysis/SwitchNonSwitch_dataset_regressions_w_callable.dta"))
# write_dta(all, paste0( path, "test Dropbox/Anya Nakhmurina/January_2022/2.2 Dataset for Analysis/SwitchNonSwitch_dataset_regressions.dta"))
write_dta(all, paste0( path, "test Dropbox/Anya Nakhmurina/January_2022/2.2 Dataset for Analysis/SwitchNonSwitch_secondary_data.dta"))
